package org.zjx.swing.db;

import com.sun.jna.platform.win32.WinDef.RECT;
import org.zjx.swing.bean.WordBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class WordBeanDB {
    private static boolean isTableCreated = false;
    private SQLiteHelper dbHelper;

    public WordBeanDB() {
        dbHelper = SQLiteHelper.getSingleton();
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;
        }
    }

    // 创建表（包含坐标和二维数组序列化字段）
    public void createTable() {
        String sql = "CREATE TABLE IF NOT EXISTS word_bean (\n"
                + "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
                + "    name TEXT,\n"
                + "    type INTEGER,\n"
                + "    top INTEGER,\n"
                + "    bottom INTEGER,\n"
                + "    left INTEGER,\n"
                + "    right INTEGER,\n"
                + "    block TEXT\n"
                + ");";
        dbHelper.createTable(sql);
    }

    // 插入数据（手动提取RECT坐标）
    public void insert(WordBean bean) {
        String sql = "INSERT INTO word_bean (name, type, top, bottom, left, right, block) VALUES (?, ?, ?, ?, ?, ?, ?)";
        Object[] params = {
                bean.getName(),
                bean.getType(),
                bean.getTop(),
                bean.getBottom(),
                bean.getLeft(),
                bean.getRight(),
                blockToString(bean.getBlock())
        };
        dbHelper.executeUpdate(sql, params);
    }

    // 根据名称删除
    public void deleteByName(String name) {
        String sql = "DELETE FROM word_bean WHERE name = ?";
        dbHelper.executeUpdate(sql, new Object[]{name});
    }

    // 查询全部（手动构建对象）
    public List<WordBean> findAll() {
        String sql = "SELECT name, type, top, bottom, left, right, block FROM word_bean";
        List<WordBean> result = new ArrayList<>();

        try (PreparedStatement pstmt = getConnection().prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                // 手动从ResultSet提取字段
                String name = rs.getString("name");
                int type = rs.getInt("type");
                int top = rs.getInt("top");
                int bottom = rs.getInt("bottom");
                int left = rs.getInt("left");
                int right = rs.getInt("right");
                String blockStr = rs.getString("block");

                // 构建RECT和二维数组
                RECT rect = new RECT();
                rect.top = top;
                rect.bottom = bottom;
                rect.left = left;
                rect.right = right;
                int[][] block = stringToBlock(blockStr);

                // 通过构造函数创建对象并设置字段
                WordBean bean = new WordBean(rect, block);
                bean.setName(name);
                bean.setType(type);
                result.add(bean);
            }
        } catch (SQLException e) {
            System.out.println("Query error: " + e.getMessage());
        }
        return result;
    }

    // 序列化二维数组为字符串
    private String blockToString(int[][] block) {
        if (block == null) return "";
        StringBuilder sb = new StringBuilder();
        for (int[] row : block) {
            for (int i = 0; i < row.length; i++) {
                sb.append(row[i]);
                if (i < row.length - 1) sb.append(",");
            }
            sb.append(";");
        }
        return sb.toString();
    }

    // 反序列化字符串为二维数组
    private int[][] stringToBlock(String str) {
        if (str == null || str.isEmpty()) return new int[0][];
        String[] rows = str.split(";");
        int[][] block = new int[rows.length][];
        for (int i = 0; i < rows.length; i++) {
            String[] cols = rows[i].split(",");
            block[i] = new int[cols.length];
            for (int j = 0; j < cols.length; j++) {
                block[i][j] = Integer.parseInt(cols[j]);
            }
        }
        return block;
    }

    // 通过反射获取数据库连接（需处理异常）
    private Connection getConnection() {
        try {
            java.lang.reflect.Field connField = SQLiteHelper.class.getDeclaredField("conn");
            connField.setAccessible(true);
            return (Connection) connField.get(dbHelper);
        } catch (Exception e) {
            throw new RuntimeException("Failed to get connection", e);
        }
    }
}
